package com.qq.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.qq.bean.Friend;
import com.qq.bean.FriendshipVO;
import com.qq.bean.Users;
import com.qq.dao.BaseDao;
import com.qq.dao.UsersDao;

public class UsersDaoImpl implements UsersDao{
	
	@Override
	public List<Users> selectAllUsers() {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		List<Users> users = new ArrayList();
		String sql = "SELECT * FROM users";

		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			while(res.next()) {
				Users user = new Users();
				user.setUserId(res.getInt("userId"));
				user.setNickName(res.getString("nickName"));
				user.setAccount(res.getString("account"));
				user.setRealName(res.getString("realName"));
				user.setPassword(res.getString("password"));
				user.setMoney(res.getDouble("money"));
				user.setAvatar(res.getString("avatar"));
				user.setCreateTime(res.getDate("createTime"));
				users.add(user);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return users;
	}

	@Override
	public Users selectUsers(String account, String password) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		Users user = null;
		String sql = "SELECT * FROM users WHERE account = ? and password = ?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, account);
			ps.setString(2, password);
			//System.out.println(ps);
			res = ps.executeQuery();
			if(res.next()) {
				user = new Users();
				user.setUserId(res.getInt("userId"));
				user.setNickName(res.getString("nickName"));
				user.setAccount(res.getString("account"));
				user.setRealName(res.getString("realName"));
				user.setPassword(res.getString("password"));
				user.setMoney(res.getDouble("money"));
				user.setAvatar(res.getString("avatar"));
				user.setCreateTime(res.getDate("createTime"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return user;
	}

	@Override
	public Users selectUsersByUsersname(String account) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		Users user = null;
		
		String sql = "SELECT * FROM users WHERE account = ?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, account);
			res = ps.executeQuery();
			while(res.next()) {
				user = new Users();
				user.setUserId(res.getInt("userId"));
				user.setNickName(res.getString("nickName"));
				user.setAccount(res.getString("account"));
				user.setRealName(res.getString("realName"));
				user.setPassword(res.getString("password"));
				user.setMoney(res.getDouble("money"));
				user.setAvatar(res.getString("avatar"));
				user.setCreateTime(res.getDate("createTime"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return user;
	}

	@Override
	public int insertFriend(Friend friend) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "INSERT INTO friend(qq_accounts,friend_accounts,add_time,State) "
				+ "VALUES (?,?,sysdate(),?)";
		
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, friend.getMyAccount());
			ps.setString(2, friend.getfAccount());
//			ps.setDate(3,new Date(friend.getAddTime().getTime()));
			ps.setInt(3, friend.getState());
			result = ps.executeUpdate();
		} catch (SQLException e) {
			
			e.printStackTrace()
			;
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}

	

	


	@Override
	public int insertUsers(Users user) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "INSERT INTO users(account,password,realName,nickName,money,avatar,createTime,role) "
				+ "VALUES (?,?,?,?,0,?,NOW(),'admin')";
		
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, user.getAccount());
			ps.setString(2, user.getPassword());
			ps.setString(3, user.getRealName());
			ps.setString(4, user.getNickName());
			ps.setString(5, user.getAvatar());
			result = ps.executeUpdate();
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}

	@Override
	public List<FriendshipVO> selectMyFriend(String myaccounts) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		List<FriendshipVO> myFriends = new ArrayList();
		String sql = "SELECT qq_accounts,friend_accounts,userid,avatar,nickName,state,add_time FROM friend AS tb1 INNER JOIN users AS tb2 "
				+ "ON tb2.account = tb1.friend_accounts "
				+ "WHERE tb1.qq_accounts = ? "
				+ "AND state = 2";
		
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, myaccounts);
			System.out.println(sql);
			res = ps.executeQuery();
			while(res.next()) {
				FriendshipVO myfriendV = new FriendshipVO();
				myfriendV.setMyaccount(res.getString("qq_accounts"));
				myfriendV.setFriendaccount(res.getString("friend_accounts"));
				myfriendV.setMyFrienduserid(res.getInt("userid"));
				myfriendV.setFirendsavater(res.getString("avatar"));
				myfriendV.setFirendNickname(res.getString("nickName"));
				myfriendV.setOurstate(res.getInt("state"));
				myfriendV.setAddTime(res.getDate("add_time"));
				myFriends.add(myfriendV);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return myFriends;
	}

	@Override
	public Users selectAddFriendUsers(String account, String friendacoount) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		Users user = null;
		
		String sql = "SELECT userid,account,avatar,nickName,state,add_time FROM friend AS tb1 INNER JOIN users AS tb2 "
				+ "ON tb2.account = tb1.friend_accounts "
				+ "WHERE tb1.qq_accounts = ? "
				+ "AND tb1.friend_accounts = ?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, account);
			ps.setString(2, friendacoount);
			res = ps.executeQuery();
			while(res.next()) {
				user = new Users();
				user.setUserId(res.getInt("userid"));
				user.setNickName(res.getString("nickName"));
				user.setAccount(res.getString("account"));
				user.setAvatar(res.getString("avatar"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return user;
	}

	@Override
	public int updateUsersAvatar(String account,String avatar) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		int rows = 0;
		String sql = "UPDATE users SET avatar = ? WHERE account = ?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, avatar);
			ps.setString(2, account);
			rows = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return rows;
	}

	@Override
	public List<Users> selectAll() {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		List<Users> users = new ArrayList();
		String sql = "SELECT * FROM users";

		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			while(res.next()) {
				Users user = new Users();
				user.setUserId(res.getInt("userid"));
				user.setNickName(res.getString("nickName"));
				user.setAccount(res.getString("account"));
				user.setRealName(res.getString("realName"));
				user.setPassword(res.getString("password"));
				user.setMoney(res.getDouble("money"));
				user.setAvatar(res.getString("avatar"));
				user.setCreateTime(res.getDate("createTime"));
				users.add(user);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return users;
	}

	@Override
	public int updateUsersbByAccount(Users user) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		int rows = 0;
		
		String sql = "UPDATE users SET nickName = ?,avatar = ?,money = ? WHERE account = ?";
		try {
				con = BaseDao.getCon();
				ps = con.prepareStatement(sql);
				ps.setString(1, user.getNickName());
				ps.setString(2, user.getAvatar());
				ps.setDouble(3, user.getMoney());
				ps.setString(4, user.getAccount());
				rows = ps.executeUpdate();
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps,null);
		}
		
		return rows;
	}

	@Override
	public int delectUsersByAccount(String account) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		int rows = 0;
		
		String sql = "DELETE FROM users WHERE account = ?";
		try {
				con = BaseDao.getCon();
				ps = con.prepareStatement(sql);
				ps.setString(1, account);
				
				rows = ps.executeUpdate();
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps,null);
		}
		
		return rows;
	}

	

	
	

	
}




